/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-23
 * @Version: 071123.001
 * Data access object of itineray class 
 */
package com.ibsol.database
{
	import com.ibsol.model.Itinerary;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class ItineraryDAO
	{
		private var _tourixDBConn:SQLConnection;
		public function ItineraryDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Loads all itineraries from local database according to package id
		 * @param:	packageid:int			itinerary's package id
		 * 			resultHandler:Function	result handler adapter
		 * 			faultHandler:Function	fault handler adapter
		 * @return:	void
		 */
		public function loadItineraiesByPackage(packageid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM itinerary WHERE packageid = " + packageid + " AND offlineOperator != 'DEL' ORDER BY sequence asc";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads specific itinerary according to itinerary id
		 * @param:	itineraryid:int			intinerary id
		 * 			resultHandler:Function	result function adapter, return itinerary object
		 * 			faultHandler:Function	fault function adapter
		 * @return:	void
		 */
		public function loadItinerary(itineraryid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM itinerary WHERE id=" + itineraryid + " AND offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					resultHandler.call(this, typeObject(sqlStr.getResult().data[0]));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Insert a new itinerary record into database
		 * Itinerary (id, packageid, breakfast, lunch, meal, offlineOperator)
		 * @param:		itinerary:Itinerary			itinerary object which will be inserted into database
		 * 				resultHandler:Function		result handler function adapter
		 * 				faultHandler:Function		fault handler function adapter
		 * @return:		void
		 */
		public function insertItinerary(itinerary:Itinerary, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO itinerary (packageid, name, desp, breakfast, lunch, meal, offlineOperator, sequence, updateTime) " + 
					"values (" + itinerary.packageid + ",'" + itinerary.name + "','" + itinerary.desp + "','" + itinerary.breakfast + "','" + itinerary.lunch + "','" + 
					itinerary.meal + "','NEW'," + itinerary.sequence + ", 'T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					itinerary.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this, itinerary.id);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes an itinerary by its own id locally, just sets its offlineOperator to "DEL"
		 * @param:		id:int						itinerary id, whom will be deleted
		 * 				resultHandler:Function		result handler function adapter
		 * 				faultHandler:Function		fault handler function adapter
		 * @return:		void
		 */
		public function deleteItineraryByID(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE itinerary SET offlineOperator='DEL', updateTime='T' WHERE id = " + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a itinerary record in database.
		 * Itinerary (id, name, packageid, desp, breakfast, lunch, meal, offlineOperator, sequence)
		 * @param:	itinerary:Itinerary				itinerary object which will be updated
		 * 			resultHandler:Function			result handler function
		 * 			faultHandler:Function			result handler function
		 * @return:	void
		 */
		public function updateItinerary(itinerary:Itinerary, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE itinerary SET name='" + itinerary.name + "', packageid=" + itinerary.packageid + ", desp='" + itinerary.desp + 
					"',breakfast='" + itinerary.breakfast + "',lunch='" + itinerary.lunch + "',meal='" + itinerary.meal + "', offlineOperator='EDIT', " + 
					"updateTime='T' WHERE id=" + itinerary.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a itinerary sequence in local database.
		 * @param:	id:int						要升级的Itinerary的id
		 * 			sequence:int				itinerary's sequence
		 * 			resultHandler:Function		result handler function
		 * 			faultHandler:Function		fault handler function
		 * @return: void
		 */
		public function updateItinerarySequence(id:int, sequence:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE itinerary SET sequence=" + sequence + ",offlineOperator='EDIT', updateTime='T' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts database object to itineray class
		 * @param:	o:Object database return object
		 * @return:	Itinerary object after conversion from database type
		 */
		public function typeObject(o:Object):Itinerary
		{
			var i:Itinerary = new Itinerary();
			i.id = o.id;
			i.name = o.name;
			i.packageid = o.packageid;
			i.desp = o.desp;
			i.breakfast = o.breakfast;
			i.lunch = o.lunch;
			i.meal = o.meal;
			i.offlineOperator = o.offlineOperator;
			i.updateTime = o.updateTime;
			i.sequence = o.sequence;
			return i;
		}
		/**
		 * Converts array object to itineray array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection ArrayCollection of itineray after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}